package nf.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import nf.entity.User;
import nf.util.ConnUtil;

public class UserDaoImplJDBC implements UserDao {
	
	private Connection conn;
	private final static String SQL_GET_COUNT = "SELECT COUNT(*) mycount FROM t_user5";
	private final static String SQL_GET_USER_LIST = "select * from (select rownum myid,t5.* from (select * from t_user5 order by id asc) t5 where rownum<=?) where myid>=?";
	private List<User> userList = new ArrayList<User>();
	
	@Override
	public int getCount() {
		int mycount = 0;
		try {
			conn = new ConnUtil().getConnection();
			PreparedStatement pst = conn.prepareStatement(SQL_GET_COUNT);
			ResultSet rs = pst.executeQuery();
			rs.next();
			mycount = rs.getInt("mycount");
			rs.close();
			pst.close();
			conn.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
			//conn.close();
		}
		return mycount;
	}

	@Override
	public List<User> getUserList(int startIndex, int endIndex) {
		try {
			conn = new ConnUtil().getConnection();
			PreparedStatement pst = conn.prepareStatement(SQL_GET_USER_LIST);
			pst.setInt(1,endIndex );
			pst.setInt(2,startIndex );
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				String userName = rs.getString("user_name");
				int sex_tmp = rs.getInt("sex");
				String sex = "未处理";
//				if (sex_tmp==1){
//					sex = "男";
//				}else if(sex_tmp==0){
//					sex = "女";
//				}else{
//					sex = "未知";
//				}
				User user = new User();
				user.setId(id);
				user.setUserName(userName);
				user.setSex_int(sex_tmp);
				user.setSex(sex);
				userList.add(user);
				//实现MVC，java代码中，不应出现界面
				//System.out.println(id+"&nbsp;"+userName+"&nbsp;"+sex+"</br>");
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		
		
		return userList;
	}

}
